package dao;

import pojo.Warehouse;
import utils.JDBCUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class WarehouseDAOImpl {

    private static final String TABLE_NAME = "Warehouse";

    public static void add(Warehouse warehouse) throws SQLException {
        String sql = "INSERT INTO " + TABLE_NAME + " VALUES (?, ?, ?, ?)";
        JDBCUtils.execute(sql, null, warehouse.getWarehouseName(),
                warehouse.getWarehouseLocation(), warehouse.getStorageSpace());
    }

    public static void delete(int warehouseNumber) throws SQLException {
        String sql = "DELETE FROM " + TABLE_NAME + " WHERE warehouse_number = ?";
        JDBCUtils.execute(sql, warehouseNumber);
    }

    public static void update(Warehouse warehouse) throws SQLException {
        String sql = "UPDATE " + TABLE_NAME + " SET warehouse_name = ?, warehouse_location = ?, storage_space = ? WHERE warehouse_number = ?";
        JDBCUtils.execute(sql, warehouse.getWarehouseName(), warehouse.getWarehouseLocation(),
                warehouse.getStorageSpace(), warehouse.getWarehouseNumber());
    }

    public static Warehouse getById(int warehouseNumber) throws SQLException {
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE warehouse_number = ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, warehouseNumber);
        if (resultSet.next()) {
            return createWarehouseFromResultSet(resultSet);
        }
        return null;
    }

    public static List<Warehouse> getAll() throws SQLException {
        List<Warehouse> warehouses = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME;
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        while (resultSet.next()) {
            Warehouse warehouse = createWarehouseFromResultSet(resultSet);
            warehouses.add(warehouse);
        }
        return warehouses;
    }

    public static int getCount() throws SQLException {
        String sql = "SELECT COUNT(*) AS count FROM " + TABLE_NAME;
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        if (resultSet.next()) {
            return resultSet.getInt("count");
        }
        return 0;
    }

    public static List<Warehouse> getByPage(int start, int pageSize) throws SQLException {
        List<Warehouse> warehouses = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, start, pageSize);
        while (resultSet.next()) {
            Warehouse warehouse = createWarehouseFromResultSet(resultSet);
            warehouses.add(warehouse);
        }
        return warehouses;
    }

    public static List<Warehouse> findByKeyword(String keyword) throws SQLException {
        List<Warehouse> warehouses = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE warehouse_name LIKE ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + keyword + "%");
        while (resultSet.next()) {
            Warehouse warehouse = createWarehouseFromResultSet(resultSet);
            warehouses.add(warehouse);
        }
        return warehouses;
    }

    public static List<Warehouse> findByPageAndKeyword(int start, int pageSize, String keyword) throws SQLException {
        List<Warehouse> warehouses = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE warehouse_name LIKE ? LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + keyword + "%", start, pageSize);
        while (resultSet.next()) {
            Warehouse warehouse = createWarehouseFromResultSet(resultSet);
            warehouses.add(warehouse);
        }
        return warehouses;
    }

    private static Warehouse createWarehouseFromResultSet(ResultSet resultSet) throws SQLException {
        int warehouseNumber = resultSet.getInt("warehouse_number");
        String warehouseName = resultSet.getString("warehouse_name");
        String warehouseLocation = resultSet.getString("warehouse_location");
        int storageSpace = resultSet.getInt("storage_space");
        return new Warehouse(warehouseNumber, warehouseName, warehouseLocation, storageSpace);
    }

}
